Imports Microsoft.VisualBasic
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System
Imports System.Data
Imports GTSDAL
Imports GTSDALTableAdapters
Imports GTSSystem.BLL.IdGenerator

Namespace GTSSystem.BLL
    <System.ComponentModel.DataObject()> _
Public Class Rates

        Private _rateAdaptor As RateTableAdapter = Nothing
        Private _rateDetailAdaptor As RateDetailTableAdapter = Nothing
        Private _rateDetailRateAdaptor As RateDetailRateTableAdapter = Nothing
        Private _rateSurchargeAdaptor As RateSurchargeTableAdapter = Nothing
        Private _rateINFOAdaptor As RateINFOTableAdapter = Nothing
        Private _contractRateINFOAdaptor As ContractRateINFOTableAdapter = Nothing
        Private _rateDetailINFOAdaptor As RateDetailINFOTableAdapter = Nothing

        Protected ReadOnly Property RateAdaptor() As RateTableAdapter
            Get
                If _rateAdaptor Is Nothing Then
                    _rateAdaptor = New RateTableAdapter
                End If
                Return _rateAdaptor
            End Get
        End Property

        Protected ReadOnly Property RateDetailAdaptor() As RateDetailTableAdapter
            Get
                If _rateDetailAdaptor Is Nothing Then
                    _rateDetailAdaptor = New RateDetailTableAdapter
                End If
                Return _rateDetailAdaptor
            End Get
        End Property


        Protected ReadOnly Property RateDetailRateAdaptor() As RateDetailRateTableAdapter
            Get
                If _rateDetailRateAdaptor Is Nothing Then
                    _rateDetailRateAdaptor = New RateDetailRateTableAdapter
                End If
                Return _rateDetailRateAdaptor
            End Get
        End Property

        Protected ReadOnly Property RateSurchargeAdaptor() As RateSurchargeTableAdapter
            Get
                If _rateSurchargeAdaptor Is Nothing Then
                    _rateSurchargeAdaptor = New RateSurchargeTableAdapter
                End If
                Return _rateSurchargeAdaptor
            End Get
        End Property

        Protected ReadOnly Property RateInfoAdaptor() As RateINFOTableAdapter
            Get
                If _rateINFOAdaptor Is Nothing Then
                    _rateINFOAdaptor = New RateINFOTableAdapter
                End If
                Return _rateINFOAdaptor
            End Get
        End Property

        Protected ReadOnly Property ContractRateInfoAdaptor() As ContractRateINFOTableAdapter
            Get
                If _contractRateINFOAdaptor Is Nothing Then
                    _contractRateINFOAdaptor = New ContractRateINFOTableAdapter
                End If
                Return _contractRateINFOAdaptor
            End Get
        End Property

        Protected ReadOnly Property RateDetailINFOAdaptor() As RateDetailINFOTableAdapter
            Get
                If _rateDetailINFOAdaptor Is Nothing Then
                    _rateDetailINFOAdaptor = New RateDetailINFOTableAdapter
                End If
                Return _rateDetailINFOAdaptor
            End Get
        End Property

        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select, True)> _
        Public Function GetRatesByContractID(ByVal RateID As Integer) As RateDataTable
            Return RateAdaptor.GetRate()
        End Function

        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select, True)> _
        Public Function GetRateByBranchID(ByVal BranchID As Integer) As RateDataTable
            Return RateAdaptor.GetRateByBranchID(BranchID)
        End Function

        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select, True)> _
        Public Function GetRateINFOByRateID(ByVal RateID As Integer) As RateINFODataTable
            Return RateInfoAdaptor.GetRateINFOByRateID(RateID)
        End Function

        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select, True)> _
        Public Function GetContractRateINFOByContractID(ByVal ContractID As Integer) As ContractRateINFODataTable
            Return ContractRateInfoAdaptor.GetContractRateINFOByContractID(ContractID)
        End Function

        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Insert, True)> _
        Public Function AddRate(ByVal Origin As Integer, _
                                ByVal Destination As Integer, _
                                ByVal ContractID As Integer) As Integer

            Dim user As MembershipUser = Membership.GetUser
            Dim p As ProfileCommon = ProfileCommon.Create(user.UserName)
            Dim Status As Integer = 1
            Dim RatePK As Integer
            Try
                RatePK = GetNextId("Rate")
                RateAdaptor.Insert(RatePK, Origin, Destination, ContractID, Status, p.BranchId)
                Return RatePK
            Catch ex As Exception
                Throw
            End Try
        End Function

        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Insert, True)> _
        Public Function AddFixedRateDetail(ByVal RateID As Integer, _
                                        ByVal ServiceTypeID As Integer, _
                                        ByVal RateTypeID As Integer, _
                                        ByVal MinWeight As Double, _
                                        ByVal MaxWeight As Double, _
                                        ByVal Price As Double) As Integer

            Dim user As MembershipUser = Membership.GetUser
            Dim p As ProfileCommon = ProfileCommon.Create(user.UserName)
            Dim Status As Integer = 1
            Dim RateDetailPK As Integer
            Try
                RateDetailPK = GetNextId("RateDetail")
                RateDetailAdaptor.InsertFixedRateDetail(RateDetailPK, ServiceTypeID, RateTypeID, MinWeight, MaxWeight, Price)
                RateDetailRateAdaptor.Insert(RateID, RateDetailPK)
            Catch ex As Exception
                Throw
            End Try
        End Function


        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Insert, True)> _
        Public Function AddBaseRateDetail(ByVal RateID As Integer, _
                                        ByVal ServiceTypeID As Integer, _
                                        ByVal RateTypeID As Integer, _
                                        ByVal MinWeight As Double, _
                                        ByVal MaxWeight As Double, _
                                        ByVal Price As Double, _
                                        ByVal BaseRate As Double) As Integer

            Dim user As MembershipUser = Membership.GetUser
            Dim p As ProfileCommon = ProfileCommon.Create(user.UserName)
            Dim Status As Integer = 1
            Dim RateDetailPK As Integer
            Try
                RateDetailPK = GetNextId("RateDetail")
                RateDetailAdaptor.InsertBaseRateDetail(RateDetailPK, ServiceTypeID, RateTypeID, MinWeight, MaxWeight, Price, BaseRate)
                RateDetailRateAdaptor.Insert(RateID, RateDetailPK)
            Catch ex As Exception
                Throw
            End Try
        End Function


        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Insert, True)> _
        Public Function AddIncrementalRateDetail(ByVal RateID As Integer, _
                                        ByVal ServiceTypeID As Integer, _
                                        ByVal RateTypeID As Integer, _
                                        ByVal MinWeight As Double, _
                                        ByVal MaxWeight As Double, _
                                        ByVal Price As Double, _
                                        ByVal MinCharge As Double) As Integer

            Dim user As MembershipUser = Membership.GetUser
            Dim p As ProfileCommon = ProfileCommon.Create(user.UserName)
            Dim Status As Integer = 1
            Dim RateDetailPK As Integer
            Try
                RateDetailPK = GetNextId("RateDetail")
                RateDetailAdaptor.InsertIncrementalRateDetail(RateDetailPK, ServiceTypeID, RateTypeID, MinWeight, MaxWeight, Price, MinCharge)
                RateDetailRateAdaptor.Insert(RateID, RateDetailPK)
            Catch ex As Exception
                Throw
            End Try
        End Function


        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Insert, True)> _
        Public Function AddRateSurcharge(ByVal RateID As Integer, _
                                            ByVal SurchargeID As Integer, _
                                            ByVal ExtraCharge As Double, _
                                            ByVal ChargeType As String) As Integer
            Try
                Return RateSurchargeAdaptor.Insert(RateID, SurchargeID, ExtraCharge, ChargeType)
            Catch ex As Exception
                Throw
            End Try
        End Function

        'The following methods deal with retrieving raw rate data and returning usable rates for a shipment
        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select, True)> _
        Public Function GetBestRates(ByVal Origin As Integer, _
                                        ByVal Destination As Integer, _
                                        ByVal Weight As Double, _
                                        ByVal ServiceType As Integer, _
                                        ByVal Surcharge As Integer) As DataSet

            Dim user As MembershipUser = Membership.GetUser
            Dim p As ProfileCommon = ProfileCommon.Create(user.UserName)
            Dim OriginProvince As String
            Dim OriginCountry As String
            Dim RateLocationLogic As New GTSSystem.BLL.RateLocation
            Dim RateLocationData As DataTable
            Try
                RateLocationData = RateLocationLogic.GetRateLocationByRateLocationID(Origin)
                OriginProvince = RateLocationData.Rows(0).Item("Province")
                OriginCountry = RateLocationData.Rows(0).Item("Country")
                Dim RData As New DataTable
                'Create the table to return to the Shipment.BLL
                Dim RatesDataSet As New DataSet
                Dim RateData As New DataTable("Rates")
                RateData.Columns.Add("CarrierID", GetType(Integer))
                RateData.Columns.Add("CarrierName", GetType(String))
                RateData.Columns.Add("Rate", GetType(Double))
                RateData.Columns.Add("Surcharge", GetType(Double))
                RateData.Columns.Add("Total", GetType(Double))
                RateData.Columns.Add("GST", GetType(Double))
                RateData.Columns.Add("PST", GetType(Double))
                RateData.Columns.Add("HST", GetType(Double))


                'Get all raw rates for the shipment by shipment details
                'get surcharges(surchargeID)
                Dim SData As New DataTable
                RData = RateInfoAdaptor.GetBestRatesByShipmentDetails(p.BranchId, ServiceType, Origin, Destination, Weight)
                If RData.Rows.Count > 0 Then

                    For n As Integer = 0 To RData.Rows.Count - 1
                        'details needed from the RateInfoDataTable to calculate rates
                        Dim RateID As Integer = RData.Rows(n).Item("RateID")
                        Dim CarrierID As Integer = RData.Rows(n).Item("CarrierID")
                        Dim CarrierName As String = RData.Rows(n).Item("CarrierName")
                        Dim RateTypeName As String = RData.Rows(n).Item("RateTypeName")
                        Dim MinWeight As Double = RData.Rows(n).Item("MinWeight")
                        'Some fields are allowed to be null we need to check to see if they are and set them to 0
                        Dim RateCharge As Double = 0
                        Dim BaseRate As Double = 0
                        Dim MinCharge As Double = 0
                        If Not IsDBNull(RData.Rows(n).Item("Price")) Then
                            RateCharge = RData.Rows(n).Item("Price")
                        End If
                        If Not IsDBNull(RData.Rows(n).Item("BaseRate")) Then
                            BaseRate = RData.Rows(n).Item("BaseRate")
                        End If
                        If Not IsDBNull(RData.Rows(n).Item("MinCharge")) Then
                            MinCharge = RData.Rows(n).Item("MinCharge")
                        End If
                        'Surcharges are produced from a separate query
                        Dim SurchargeLogic As New GTSSystem.BLL.Surcharge
                        SData = SurchargeLogic.GetSurchargeByRateIDSurchargeID(RateID, Surcharge)
                        Dim Charge As Double = 0
                        Dim ExtraCharge As Double = 0
                        Dim ChargeType As String = ""
                        If (SData.Rows.Count > 0) Then
                            ExtraCharge = SData.Rows(0).Item("ExtraCharge")
                            ChargeType = SData.Rows(0).Item("ChargeType")

                        End If
                        Dim Rate As Double = 0
                        Dim GST As Double = 0
                        Dim PST As Double = 0
                        Dim HST As Double = 0
                        Dim Total As Double = 0

                        'Calculating Total Rate by it's rate type
                        Select Case RateTypeName
                            Case Is = "Base Rate"
                                'Base rate + (Weight - minWeight) * RateCharge
                                Rate = BaseRate + ((Weight - MinWeight) * RateCharge)
                                Select Case ChargeType
                                    Case Is = "Fixed"
                                        Charge = ExtraCharge
                                    Case Is = "Percentage"
                                        Charge = ExtraCharge / 100 * Rate
                                End Select
                                Total = Rate + Charge
                                'retrieve tax info
                                Dim TaxTable As DataTable
                                TaxTable = CalculateTaxes(Total, OriginProvince, OriginCountry)
                                GST = TaxTable.Rows(0).Item("GST")
                                PST = TaxTable.Rows(0).Item("PST")
                                HST = TaxTable.Rows(0).Item("HST")

                            Case Is = "Incremental"
                                Rate = Weight * RateCharge
                                If Rate > MinCharge Then
                                    Rate = Rate
                                Else
                                    Rate = MinCharge
                                End If
                                Select Case ChargeType
                                    Case Is = "Fixed"
                                        Charge = ExtraCharge
                                    Case Is = "Percentage"
                                        Charge = ExtraCharge / 100 * Rate
                                End Select
                                Total = Rate + Charge
                                'retrieve tax info
                                Dim TaxTable As DataTable
                                TaxTable = CalculateTaxes(Total, OriginProvince, OriginCountry)
                                GST = TaxTable.Rows(0).Item("GST")
                                PST = TaxTable.Rows(0).Item("PST")
                                HST = TaxTable.Rows(0).Item("HST")

                            Case Is = "Fixed"
                                Rate = RateCharge
                                Select Case ChargeType
                                    Case Is = "Fixed"
                                        Charge = ExtraCharge
                                    Case Is = "Percentage"
                                        Charge = ExtraCharge / 100 * Rate
                                End Select
                                Total = Rate + Charge
                                Dim TaxTable As DataTable
                                'retrieve tax info
                                TaxTable = CalculateTaxes(Total, OriginProvince, OriginCountry)
                                GST = TaxTable.Rows(0).Item("GST")
                                PST = TaxTable.Rows(0).Item("PST")
                                HST = TaxTable.Rows(0).Item("HST")

                        End Select
                        'Add calculated values to a row in the RateTable
                        Dim aRow As DataRow
                        aRow = RateData.NewRow
                        aRow.Item("CarrierID") = CarrierID
                        aRow.Item("CarrierName") = CarrierName
                        aRow.Item("Rate") = Rate
                        aRow.Item("Surcharge") = Charge
                        aRow.Item("Total") = Total
                        aRow.Item("GST") = GST
                        aRow.Item("PST") = PST
                        aRow.Item("HST") = HST
                        RateData.Rows.Add(aRow)
                    Next
                    RatesDataSet.Tables.Add(RateData)
                    Return RatesDataSet
                Else
                    Return RatesDataSet
                End If
            Catch ex As Exception
                Throw
            End Try
        End Function

        Public Function CalculateTaxes(ByVal Total As Double, ByVal Province As String, ByVal Country As String) As DataTable

            Dim GST As Double = 0
            Dim PST As Double = 0
            Dim HST As Double = 0
            Dim MyTaxes As New DataTable("Taxes")
            MyTaxes.Columns.Add("GST", GetType(Double))
            MyTaxes.Columns.Add("PST", GetType(Double))
            MyTaxes.Columns.Add("HST", GetType(Double))

            If Country.ToUpper = "CANADA" Then
                Try

                    If Province.ToUpper = "NL" Or Province.ToUpper = "NS" Or Province.ToUpper = "NB" _
                    Or Province.ToUpper = "NEWFOUNDLAND AND LABRADOR" Or Province.ToUpper = "NOVA SCOTIA" Or Province.ToUpper = "NEW BRUNSWICK" Then
                        HST = Total * 0.14
                    Else
                        GST = Total * 0.06
                        If Province.ToUpper = "BC" Or Province.ToUpper = "SK" Or Province.ToUpper = "MB" _
                        Or Province.ToUpper = "BRITISH COLUMBIA" Or Province.ToUpper = "SASKATCHEWAN" Or Province.ToUpper = "MANITOBA" Then
                            PST = Total * 0.07
                        Else
                            If Province.ToUpper = "ON" Or Province.ToUpper = "ONTARIO" Then
                                PST = Total * 0.08
                            Else
                                If Province.ToUpper = "PE" Or Province.ToUpper = "PRINCE EDWARD ISLAND" Then
                                    PST = Total * 0.1
                                Else
                                    If Province.ToUpper = "QC" Or Province.ToUpper = "QUEBEC" Then
                                        PST = (Total + GST) * 0.075
                                    End If
                                End If
                            End If
                        End If
                    End If
                Catch ex As Exception
                    Throw
                End Try
            End If
            Dim aRow As DataRow
            aRow = MyTaxes.NewRow
            aRow.Item("GST") = GST
            aRow.Item("PST") = PST
            aRow.Item("HST") = HST
            MyTaxes.Rows.Add(aRow)
            Return MyTaxes
        End Function
        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Update, True)> _
        Public Sub UpdateBaseRate(ByVal RateDetailID As Integer, ByVal RateCharge As Double, ByVal BaseRate As Double)
            Try
                RateDetailAdaptor.UpdateBaseRate(RateCharge, BaseRate, RateDetailID)
            Catch ex As Exception
                Throw
            End Try
        End Sub

        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Update, True)> _
        Public Sub UpdateFixedRate(ByVal RateDetailID As Integer, ByVal RateCharge As Double)
            Try
                RateDetailAdaptor.UpdateFixedRate(RateCharge, RateDetailID)
            Catch ex As Exception
                Throw
            End Try
        End Sub

        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Update, True)> _
        Public Sub UpdateIncrementalRate(ByVal RateDetailID As Integer, ByVal RateCharge As Double, ByVal MinCharge As Double)
            Try
                RateDetailAdaptor.UpdateIncrementalRate(RateCharge, MinCharge, RateDetailID)
            Catch ex As Exception
                Throw
            End Try
        End Sub

        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Update, True)> _
        Public Sub UpdateRateDetail(ByVal RateCharge As Double, ByVal MinCharge As Double, _
                                    ByVal BaseRate As Double, ByVal MinWeight As Double, _
                                    ByVal MaxWeight As Double, ByVal Price As Double, _
                                    ByVal RateDetailID As Integer)
            'RateCharge, MinCharge, BaseRate, MinWeight, MaxWeight, Price, RateDetailID.
            Try
                If MinCharge.ToString.Trim = "" And BaseRate.ToString.Trim = "" Then
                    RateDetailAdaptor.UpdateFixedRate(Price, RateDetailID)
                Else
                    If MinCharge.ToString.Trim = "" Then
                        RateDetailAdaptor.UpdateBaseRate(Price, BaseRate, RateDetailID)
                    Else
                        RateDetailAdaptor.UpdateIncrementalRate(Price, MinCharge, RateDetailID)
                    End If
                End If
            Catch
                Throw
            End Try
        End Sub

        <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select, True)> _
        Public Function GetRateDetailINFOByRateID(ByVal RateID As Integer) As RateDetailINFODataTable
            Try
                Return RateDetailINFOAdaptor.GetRateDetailINFOByRateID(RateID)
            Catch ex As Exception
                Throw
            End Try
        End Function
    End Class
End Namespace
